using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class InvoiceRegisterDataAccess : Connection
    {
        #region Local Variable


        private string _strSPGetName = "dbo.[procInvoiceRegister_get]";
        private string _strSPGetAllName = "dbo.[procInvoiceRegister_getall]";
        private string _strSPGetPages = "dbo.[procInvoiceRegister_getpaged]";
        private string _strSPIsExist = "dbo.[procInvoiceRegister_isexist]";
        private string _strTableName = "InvoiceRegister";
        private string _strSPIsExistOurRef = "dbo.[procInvoiceRegister_isexistOurRef]";
        private string _strSPIsExistOurRef1 = "dbo.[procInvoiceRegister_isexistOurRef1]";



        private string _strSPGetName1 = "dbo.[procInvoiceRegister1_get]";
        private string _strSPGetAllName1 = "dbo.[procInvoiceRegister1_getall]";
        private string _strSPGetPages1 = "dbo.[procInvoiceRegister1_getpaged]";
        private string _strSPIsExist1 = "dbo.[procInvoiceRegister1_isexist]";

        private string _strSPGetCondName = "dbo.[procInvoiceRegister_getCond]";
        private string _strSPGetCondName1 = "dbo.[procInvoiceRegister1_getCond]";

        #endregion Local Variable

        #region Method
        // public InvoiceRegisterInfo Get_InvoiceRegister(
        //     String InvNo,
        //     String Creditor,
        //     Int16 LineNo
        // , ref string sErr)
        // {
        //     InvoiceRegisterInfo objEntr = new InvoiceRegisterInfo();
        //     connect();
        //     InitSPCommand(_strSPGetName);
        //     AddParameter("InvNo", InvNo);
        //     AddParameter("Creditor", Creditor);
        //     AddParameter("LineNo", LineNo);

        //     DataTable list = new DataTable();
        //     try
        //     {
        //         list = executeSelectSP(command);
        //     }
        //     catch (Exception ex)
        //     {
        //         sErr = ex.Message;
        //     }
        //     disconnect();

        //     if (list.Rows.Count > 0)
        //         objEntr = (InvoiceRegisterInfo)GetDataFromDataRow(list, 0);
        //     //if (dr != null) list = CBO.FillCollection(dr, ref list);
        //     if (sErr != "") ErrorLog.SetLog(sErr);
        //     return objEntr;
        // }

        // public InvoiceRegisterInfo Get_InvoiceRegister1(
        //    String InvNo,
        //    String Creditor,
        //    Int16 LineNo
        //, ref string sErr)
        // {
        //     InvoiceRegisterInfo objEntr = new InvoiceRegisterInfo();
        //     connect();
        //     InitSPCommand(_strSPGetName1);
        //     AddParameter("InvNo", InvNo);
        //     AddParameter("Creditor", Creditor);
        //     AddParameter("LineNo", LineNo);

        //     DataTable list = new DataTable();
        //     try
        //     {
        //         list = executeSelectSP(command);
        //     }
        //     catch (Exception ex)
        //     {
        //         sErr = ex.Message;
        //     }
        //     disconnect();

        //     if (list.Rows.Count > 0)
        //         objEntr = (InvoiceRegisterInfo)GetDataFromDataRow(list, 0);
        //     //if (dr != null) list = CBO.FillCollection(dr, ref list);
        //     if (sErr != "") ErrorLog.SetLog(sErr);
        //     return objEntr;
        // }

        public DataTable GetCondHL(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetCondHV(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }



        public Boolean IsExist_InvoiceRegisterOurRef(String OurRef)
        {
            InitConnect();
            InitSPCommand(_strSPIsExistOurRef);

            AddParameter("OurRef", OurRef);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            return list.Rows.Count == 1;
        }
        public Boolean IsExist_InvoiceRegisterOurRef1(String OurRef)
        {
            InitConnect();
            InitSPCommand(_strSPIsExistOurRef1);

            AddParameter("OurRef", OurRef);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            return list.Rows.Count == 1;
        }

        public DataTable GetAll_InvoiceRegister(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetAll_InvoiceRegister1(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName1);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_InvoiceRegister(InvoiceRegisterInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO InvoiceRegister
                      (Reference, InvNo, Creditor, OurRef, IssueDate, ReceiptDate, Amt, OtherAmt, Currency, PymtRef, PaidDate, PaymentTerm, DueDate, Description, Contract, 
                      DeliveryDate, ReturnedDate, Department, Remark1, Remark2, Remark3, Mark, TransMitRef, TransMitDate, MarkTrm, TransMitStat, Receiver, VendorCode, [LineNo], 
                      CvtRate, ReturnRef, Budget, VAT, VATOther, VATPercent, Originality, CIT, PayableAmt, ExpenseAmt, aYear, PayableAmtOther, Business, Period, Status, Voucher, 
                      ACCNT_CODE, InvSN, Employee, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate)
                VALUES     (@Reference,@InvNo,@Creditor,@OurRef,@IssueDate,@ReceiptDate,@Amt,@OtherAmt,@Currency,@PymtRef,@PaidDate,@PaymentTerm,@DueDate,@Description,@Contract,@DeliveryDate,@ReturnedDate,@Department,@Remark1,@Remark2,@Remark3,@Mark,@TransMitRef,@TransMitDate,@MarkTrm,@TransMitStat,@Receiver,@VendorCode,@LineNo,@CvtRate,@ReturnRef,@Budget,@VAT,@VATOther,@VATPercent,@Originality,@CIT,@PayableAmt,@ExpenseAmt,@aYear,@PayableAmtOther,@Business,@Period,@Status,@Voucher,@ACCNT_CODE,@InvSN,@Employee,@CreatedBy,@ModifiedBy,@CreatedDate,@ModifiedDate)");
            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);

            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);

            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);

            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);

            //AddParameter("Amt", objEntr.Amt);
            if (objEntr.Amt == 0)
                AddParameter("Amt", DBNull.Value);
            else
                AddParameter("Amt", objEntr.Amt);

            //AddParameter("OtherAmt", objEntr.OtherAmt);
            if (objEntr.OtherAmt == 0)
                AddParameter("OtherAmt", DBNull.Value);
            else
                AddParameter("OtherAmt", objEntr.OtherAmt);

            //AddParameter("Currency", objEntr.Currency);
            if (objEntr.Currency == "")
                AddParameter("Currency", DBNull.Value);
            else
                AddParameter("Currency", objEntr.Currency);

            //AddParameter("PymtRef", objEntr.PymtRef);
            if (objEntr.PymtRef == "")
                AddParameter("PymtRef", DBNull.Value);
            else
                AddParameter("PymtRef", objEntr.PymtRef);

            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);

            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == -1)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);

            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);

            //AddParameter("Description", objEntr.Description);
            if (objEntr.Description == "")
                AddParameter("Description", DBNull.Value);
            else
                AddParameter("Description", objEntr.Description);

            //AddParameter("Contract", objEntr.Contract);
            if (objEntr.Contract == "")
                AddParameter("Contract", DBNull.Value);
            else
                AddParameter("Contract", objEntr.Contract);

            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);

            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);

            //AddParameter("Department", objEntr.Department);
            if (objEntr.Department == "")
                AddParameter("Department", DBNull.Value);
            else
                AddParameter("Department", objEntr.Department);

            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);

            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);

            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);

            //AddParameter("Mark", objEntr.Mark);
            if (objEntr.Mark == -1)
                AddParameter("Mark", DBNull.Value);
            else
                AddParameter("Mark", objEntr.Mark);

            //AddParameter("TransMitRef", objEntr.TransMitRef);
            if (objEntr.TransMitRef == "")
                AddParameter("TransMitRef", DBNull.Value);
            else
                AddParameter("TransMitRef", objEntr.TransMitRef);

            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);

            //AddParameter("MarkTrm", objEntr.MarkTrm);
            if (objEntr.MarkTrm == -1)
                AddParameter("MarkTrm", DBNull.Value);
            else
                AddParameter("MarkTrm", objEntr.MarkTrm);

            //AddParameter("TransMitStat", objEntr.TransMitStat);
            if (objEntr.TransMitStat == "")
                AddParameter("TransMitStat", DBNull.Value);
            else
                AddParameter("TransMitStat", objEntr.TransMitStat);

            //AddParameter("Receiver", objEntr.Receiver);
            if (objEntr.Receiver == "")
                AddParameter("Receiver", DBNull.Value);
            else
                AddParameter("Receiver", objEntr.Receiver);

            //AddParameter("VendorCode", objEntr.VendorCode);
            if (objEntr.VendorCode == "")
                AddParameter("VendorCode", DBNull.Value);
            else
                AddParameter("VendorCode", objEntr.VendorCode);

            //AddParameter("LineNo", objEntr.LineNo);
            if (objEntr.LineNo == 0)
                AddParameter("LineNo", 1);
            else
                AddParameter("LineNo", objEntr.LineNo);

            //AddParameter("CvtRate", objEntr.CvtRate);
            if (objEntr.CvtRate == 0)
                AddParameter("CvtRate", DBNull.Value);
            else
                AddParameter("CvtRate", objEntr.CvtRate);

            //AddParameter("ReturnRef", objEntr.ReturnRef);
            if (objEntr.ReturnRef == "")
                AddParameter("ReturnRef", DBNull.Value);
            else
                AddParameter("ReturnRef", objEntr.ReturnRef);

            //AddParameter("Budget", objEntr.Budget);
            if (objEntr.Budget == "")
                AddParameter("Budget", DBNull.Value);
            else
                AddParameter("Budget", objEntr.Budget);

            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);

            //AddParameter("VATOther", objEntr.VATOther);
            if (objEntr.VATOther == 0)
                AddParameter("VATOther", DBNull.Value);
            else
                AddParameter("VATOther", objEntr.VATOther);

            //AddParameter("VATPercent", objEntr.VATPercent);
            if (objEntr.VATPercent == 0)
                AddParameter("VATPercent", DBNull.Value);
            else
                AddParameter("VATPercent", objEntr.VATPercent);

            //AddParameter("Originality", objEntr.Originality);
            if (objEntr.Originality == "")
                AddParameter("Originality", DBNull.Value);
            else
                AddParameter("Originality", objEntr.Originality);

            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);

            //AddParameter("PayableAmt", objEntr.PayableAmt);
            if (objEntr.PayableAmt == 0)
                AddParameter("PayableAmt", DBNull.Value);
            else
                AddParameter("PayableAmt", objEntr.PayableAmt);

            //AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            if (objEntr.ExpenseAmt == 0)
                AddParameter("ExpenseAmt", DBNull.Value);
            else
                AddParameter("ExpenseAmt", objEntr.ExpenseAmt);

            //AddParameter("aYear", objEntr.aYear);
            if (objEntr.aYear == 0)
                AddParameter("aYear", DBNull.Value);
            else
                AddParameter("aYear", objEntr.aYear);

            //AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            if (objEntr.PayableAmtOther == 0)
                AddParameter("PayableAmtOther", DBNull.Value);
            else
                AddParameter("PayableAmtOther", objEntr.PayableAmtOther);

            //AddParameter("Business", objEntr.Business);
            if (objEntr.Business == "")
                AddParameter("Business", DBNull.Value);
            else
                AddParameter("Business", objEntr.Business);

            //AddParameter("Period", objEntr.Period);
            if (objEntr.Period == -1)
                AddParameter("Period", DBNull.Value);
            else
                AddParameter("Period", objEntr.Period);

            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);

            //AddParameter("Voucher", objEntr.Voucher);
            if (objEntr.Voucher == "")
                AddParameter("Voucher", DBNull.Value);
            else
                AddParameter("Voucher", objEntr.Voucher);

            //AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            if (objEntr.ACCNT_CODE == "")
                AddParameter("ACCNT_CODE", DBNull.Value);
            else
                AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);

            //AddParameter("InvSN", objEntr.InvSN);
            if (objEntr.InvSN == "")
                AddParameter("InvSN", DBNull.Value);
            else
                AddParameter("InvSN", objEntr.InvSN);

            //AddParameter("Employee", objEntr.Employee);
            if (objEntr.Employee == "")
                AddParameter("Employee", DBNull.Value);
            else
                AddParameter("Employee", objEntr.Employee);

            //AddParameter("CreatedBy", objEntr.CreatedBy);
            if (objEntr.CreatedBy == "")
                AddParameter("CreatedBy", DBNull.Value);
            else
                AddParameter("CreatedBy", objEntr.CreatedBy);

            //AddParameter("ModifiedBy", objEntr.ModifiedBy);
            if (objEntr.ModifiedBy == "")
                AddParameter("ModifiedBy", DBNull.Value);
            else
                AddParameter("ModifiedBy", objEntr.ModifiedBy);

            //AddParameter("CreatedDate", objEntr.CreatedDate);
            if (objEntr.CreatedDate == DateTime.MinValue)
                AddParameter("CreatedDate", DBNull.Value);
            else
                AddParameter("CreatedDate", objEntr.CreatedDate);

            AddParameter("ModifiedDate", DateTime.Now);
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        public Int32 Add_InvoiceRegister1(InvoiceRegisterInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO InvoiceRegister1
                      (Reference, InvNo, Creditor, OurRef, IssueDate, ReceiptDate, Amt, OtherAmt, Currency, PymtRef, PaidDate, PaymentTerm, DueDate, Description, Contract, 
                      DeliveryDate, ReturnedDate, Department, Remark1, Remark2, Remark3, Mark, TransMitRef, TransMitDate, MarkTrm, TransMitStat, Receiver, VendorCode, [LineNo], 
                      CvtRate, ReturnRef, Budget, VAT, VATOther, VATPercent, Originality, CIT, PayableAmt, ExpenseAmt, aYear, PayableAmtOther, Business, Period, Status, Voucher, 
                      ACCNT_CODE, InvSN, Employee, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate)
                VALUES     (@Reference,@InvNo,@Creditor,@OurRef,@IssueDate,@ReceiptDate,@Amt,@OtherAmt,@Currency,@PymtRef,@PaidDate,@PaymentTerm,@DueDate,@Description,@Contract,@DeliveryDate,@ReturnedDate,@Department,@Remark1,@Remark2,@Remark3,@Mark,@TransMitRef,@TransMitDate,@MarkTrm,@TransMitStat,@Receiver,@VendorCode,@LineNo,@CvtRate,@ReturnRef,@Budget,@VAT,@VATOther,@VATPercent,@Originality,@CIT,@PayableAmt,@ExpenseAmt,@aYear,@PayableAmtOther,@Business,@Period,@Status,@Voucher,@ACCNT_CODE,@InvSN,@Employee,@CreatedBy,@ModifiedBy,@CreatedDate,@ModifiedDate)");

            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            //AddParameter("Amt", objEntr.Amt);
            if (objEntr.Amt == 0)
                AddParameter("Amt", DBNull.Value);
            else
                AddParameter("Amt", objEntr.Amt);
            //AddParameter("OtherAmt", objEntr.OtherAmt);
            if (objEntr.OtherAmt == 0)
                AddParameter("OtherAmt", DBNull.Value);
            else
                AddParameter("OtherAmt", objEntr.OtherAmt);
            //AddParameter("Currency", objEntr.Currency);
            if (objEntr.Currency == "")
                AddParameter("Currency", DBNull.Value);
            else
                AddParameter("Currency", objEntr.Currency);
            //AddParameter("PymtRef", objEntr.PymtRef);
            if (objEntr.PymtRef == "")
                AddParameter("PymtRef", DBNull.Value);
            else
                AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == -1)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            //AddParameter("Description", objEntr.Description);
            if (objEntr.Description == "")
                AddParameter("Description", DBNull.Value);
            else
                AddParameter("Description", objEntr.Description);
            //AddParameter("Contract", objEntr.Contract);
            if (objEntr.Contract == "")
                AddParameter("Contract", DBNull.Value);
            else
                AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            //AddParameter("Department", objEntr.Department);
            if (objEntr.Department == "")
                AddParameter("Department", DBNull.Value);
            else
                AddParameter("Department", objEntr.Department);
            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);
            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);
            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);
            //AddParameter("Mark", objEntr.Mark);
            if (objEntr.Mark == -1)
                AddParameter("Mark", DBNull.Value);
            else
                AddParameter("Mark", objEntr.Mark);
            //AddParameter("TransMitRef", objEntr.TransMitRef);
            if (objEntr.TransMitRef == "")
                AddParameter("TransMitRef", DBNull.Value);
            else
                AddParameter("TransMitRef", objEntr.TransMitRef);
            //AddParameter("TransMitDate", objEntr.TransMitDate);
            if (objEntr.TransMitDate == DateTime.MinValue)
                AddParameter("TransMitDate", DBNull.Value);
            else
                AddParameter("TransMitDate", objEntr.TransMitDate);
            //AddParameter("MarkTrm", objEntr.MarkTrm);
            if (objEntr.MarkTrm == -1)
                AddParameter("MarkTrm", DBNull.Value);
            else
                AddParameter("MarkTrm", objEntr.MarkTrm);
            //AddParameter("TransMitStat", objEntr.TransMitStat);
            if (objEntr.TransMitStat == "")
                AddParameter("TransMitStat", DBNull.Value);
            else
                AddParameter("TransMitStat", objEntr.TransMitStat);
            //AddParameter("Receiver", objEntr.Receiver);
            if (objEntr.Receiver == "")
                AddParameter("Receiver", DBNull.Value);
            else
                AddParameter("Receiver", objEntr.Receiver);
            //AddParameter("VendorCode", objEntr.VendorCode);
            if (objEntr.VendorCode == "")
                AddParameter("VendorCode", DBNull.Value);
            else
                AddParameter("VendorCode", objEntr.VendorCode);
            //AddParameter("LineNo", objEntr.LineNo);
            if (objEntr.LineNo == 0)
                AddParameter("LineNo", 1);
            else
                AddParameter("LineNo", objEntr.LineNo);
            //AddParameter("CvtRate", objEntr.CvtRate);
            if (objEntr.CvtRate == 0)
                AddParameter("CvtRate", DBNull.Value);
            else
                AddParameter("CvtRate", objEntr.CvtRate);
            //AddParameter("ReturnRef", objEntr.ReturnRef);
            if (objEntr.ReturnRef == "")
                AddParameter("ReturnRef", DBNull.Value);
            else
                AddParameter("ReturnRef", objEntr.ReturnRef);
            //AddParameter("Budget", objEntr.Budget);
            if (objEntr.Budget == "")
                AddParameter("Budget", DBNull.Value);
            else
                AddParameter("Budget", objEntr.Budget);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);
            //AddParameter("VATOther", objEntr.VATOther);
            if (objEntr.VATOther == 0)
                AddParameter("VATOther", DBNull.Value);
            else
                AddParameter("VATOther", objEntr.VATOther);
            //AddParameter("VATPercent", objEntr.VATPercent);
            if (objEntr.VATPercent == 0)
                AddParameter("VATPercent", DBNull.Value);
            else
                AddParameter("VATPercent", objEntr.VATPercent);
            //AddParameter("Originality", objEntr.Originality);
            if (objEntr.Originality == "")
                AddParameter("Originality", DBNull.Value);
            else
                AddParameter("Originality", objEntr.Originality);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("PayableAmt", objEntr.PayableAmt);
            if (objEntr.PayableAmt == 0)
                AddParameter("PayableAmt", DBNull.Value);
            else
                AddParameter("PayableAmt", objEntr.PayableAmt);
            //AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            if (objEntr.ExpenseAmt == 0)
                AddParameter("ExpenseAmt", DBNull.Value);
            else
                AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            //AddParameter("aYear", objEntr.aYear);
            if (objEntr.aYear == 0)
                AddParameter("aYear", DBNull.Value);
            else
                AddParameter("aYear", objEntr.aYear);
            //AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            if (objEntr.PayableAmtOther == 0)
                AddParameter("PayableAmtOther", DBNull.Value);
            else
                AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            //AddParameter("Business", objEntr.Business);
            if (objEntr.Business == "")
                AddParameter("Business", DBNull.Value);
            else
                AddParameter("Business", objEntr.Business);
            //AddParameter("Period", objEntr.Period);
            if (objEntr.Period == -1)
                AddParameter("Period", DBNull.Value);
            else
                AddParameter("Period", objEntr.Period);
            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);
            //AddParameter("Voucher", objEntr.Voucher);
            if (objEntr.Voucher == "")
                AddParameter("Voucher", DBNull.Value);
            else
                AddParameter("Voucher", objEntr.Voucher);
            //AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            if (objEntr.ACCNT_CODE == "")
                AddParameter("ACCNT_CODE", DBNull.Value);
            else
                AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            //AddParameter("InvSN", objEntr.InvSN);
            if (objEntr.InvSN == "")
                AddParameter("InvSN", DBNull.Value);
            else
                AddParameter("InvSN", objEntr.InvSN);
            //AddParameter("Employee", objEntr.Employee);
            if (objEntr.Employee == "")
                AddParameter("Employee", DBNull.Value);
            else
                AddParameter("Employee", objEntr.Employee);
            //AddParameter("CreatedBy", objEntr.CreatedBy);
            if (objEntr.CreatedBy == "")
                AddParameter("CreatedBy", DBNull.Value);
            else
                AddParameter("CreatedBy", objEntr.CreatedBy);
            //AddParameter("ModifiedBy", objEntr.ModifiedBy);
            if (objEntr.ModifiedBy == "")
                AddParameter("ModifiedBy", DBNull.Value);
            else
                AddParameter("ModifiedBy", objEntr.ModifiedBy);
            //AddParameter("CreatedDate", objEntr.CreatedDate);
            if (objEntr.CreatedDate == DateTime.MinValue)
                AddParameter("CreatedDate", DBNull.Value);
            else
                AddParameter("CreatedDate", objEntr.CreatedDate);
            AddParameter("ModifiedDate", DateTime.Now);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_InvoiceRegister(InvoiceRegisterInfo objEntr)
        {

            string sql = @"UPDATE InvoiceRegister SET 
                    OurRef      = @OurRef,
                    IssueDate   = @IssueDate,
                    ReceiptDate = @ReceiptDate,
                    Amt         = @Amt,
                    OtherAmt    = @OtherAmt,
                    Currency    = @Currency,
                    PymtRef     = @PymtRef,
                    PaidDate    = @PaidDate,
                    ReturnedDate= @ReturnedDate,
                    PaymentTerm = @PaymentTerm,
                    DueDate     = @DueDate,
                    Description = @Description,
                    Contract    = @Contract,
                    DeliveryDate= @DeliveryDate,                 
                    Department  = @Department,
                    Remark1     = @Remark1,
                    Remark2     = @Remark2,
                    Remark3     = @Remark3,
                    Mark        = @Mark,
                    MarkTrm     = @MarkTrm,                            
                    VendorCode  = @VendorCode,
                    CvtRate     = @CvtRate,                  
                    Budget      = @Budget,
                    VAT         = @VAT,
                    VATOther    = @VATOther,
                    VATPercent  = @VATPercent,
                    Originality = @Originality,
                    CIT         = @CIT,
                    PayableAmt  = @PayableAmt,
                    ExpenseAmt  = @ExpenseAmt,
                    aYear       = @aYear,
                    PayableAmtOther = @PayableAmtOther,
                    Business    = @Business,
                    Period      = @Period,
                    Status      = @Status,
                    Voucher     = @Voucher,
                    ACCNT_CODE  = @ACCNT_CODE,
                    InvSN       = @InvSN,
                    Employee    = @Employee,                   
                    ModifiedBy  = @ModifiedBy,                  
                    ModifiedDate= @ModifiedDate,
                    InvNo = @InvNo,
                    Creditor = @Creditor	
                WHERE Reference = @Reference AND [LineNo] = @LineNo";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            //AddParameter("Amt", objEntr.Amt);
            if (objEntr.Amt == 0)
                AddParameter("Amt", DBNull.Value);
            else
                AddParameter("Amt", objEntr.Amt);
            //AddParameter("OtherAmt", objEntr.OtherAmt);
            if (objEntr.OtherAmt == 0)
                AddParameter("OtherAmt", DBNull.Value);
            else
                AddParameter("OtherAmt", objEntr.OtherAmt);
            //AddParameter("Currency", objEntr.Currency);
            if (objEntr.Currency == "")
                AddParameter("Currency", DBNull.Value);
            else
                AddParameter("Currency", objEntr.Currency);
            //AddParameter("PymtRef", objEntr.PymtRef);
            if (objEntr.PymtRef == "")
                AddParameter("PymtRef", DBNull.Value);
            else
                AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == -1)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            //AddParameter("Description", objEntr.Description);
            if (objEntr.Description == "")
                AddParameter("Description", DBNull.Value);
            else
                AddParameter("Description", objEntr.Description);
            //AddParameter("Contract", objEntr.Contract);
            if (objEntr.Contract == "")
                AddParameter("Contract", DBNull.Value);
            else
                AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);

            //AddParameter("Department", objEntr.Department);
            if (objEntr.Department == "")
                AddParameter("Department", DBNull.Value);
            else
                AddParameter("Department", objEntr.Department);
            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);
            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);
            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);
            //AddParameter("Mark", objEntr.Mark);
            if (objEntr.Mark == -1)
                AddParameter("Mark", DBNull.Value);
            else
                AddParameter("Mark", objEntr.Mark);


            //AddParameter("MarkTrm", objEntr.MarkTrm);
            if (objEntr.MarkTrm == -1)
                AddParameter("MarkTrm", DBNull.Value);
            else
                AddParameter("MarkTrm", objEntr.MarkTrm);


            //AddParameter("VendorCode", objEntr.VendorCode);
            if (objEntr.VendorCode == "")
                AddParameter("VendorCode", DBNull.Value);
            else
                AddParameter("VendorCode", objEntr.VendorCode);
            //AddParameter("LineNo", objEntr.LineNo);
            if (objEntr.LineNo == 0)
                AddParameter("LineNo", 1);
            else
                AddParameter("LineNo", objEntr.LineNo);
            //AddParameter("CvtRate", objEntr.CvtRate);
            if (objEntr.CvtRate == 0)
                AddParameter("CvtRate", DBNull.Value);
            else
                AddParameter("CvtRate", objEntr.CvtRate);

            //AddParameter("Budget", objEntr.Budget);
            if (objEntr.Budget == "")
                AddParameter("Budget", DBNull.Value);
            else
                AddParameter("Budget", objEntr.Budget);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);
            //AddParameter("VATOther", objEntr.VATOther);
            if (objEntr.VATOther == 0)
                AddParameter("VATOther", DBNull.Value);
            else
                AddParameter("VATOther", objEntr.VATOther);
            //AddParameter("VATPercent", objEntr.VATPercent);
            if (objEntr.VATPercent == 0)
                AddParameter("VATPercent", DBNull.Value);
            else
                AddParameter("VATPercent", objEntr.VATPercent);
            //AddParameter("Originality", objEntr.Originality);
            if (objEntr.Originality == "")
                AddParameter("Originality", DBNull.Value);
            else
                AddParameter("Originality", objEntr.Originality);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("PayableAmt", objEntr.PayableAmt);
            if (objEntr.PayableAmt == 0)
                AddParameter("PayableAmt", DBNull.Value);
            else
                AddParameter("PayableAmt", objEntr.PayableAmt);
            //AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            if (objEntr.ExpenseAmt == 0)
                AddParameter("ExpenseAmt", DBNull.Value);
            else
                AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            //AddParameter("aYear", objEntr.aYear);
            if (objEntr.aYear == 0)
                AddParameter("aYear", DBNull.Value);
            else
                AddParameter("aYear", objEntr.aYear);
            //AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            if (objEntr.PayableAmtOther == 0)
                AddParameter("PayableAmtOther", DBNull.Value);
            else
                AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            //AddParameter("Business", objEntr.Business);
            if (objEntr.Business == "")
                AddParameter("Business", DBNull.Value);
            else
                AddParameter("Business", objEntr.Business);
            //AddParameter("Period", objEntr.Period);
            if (objEntr.Period == -1)
                AddParameter("Period", DBNull.Value);
            else
                AddParameter("Period", objEntr.Period);
            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);
            //AddParameter("Voucher", objEntr.Voucher);
            if (objEntr.Voucher == "")
                AddParameter("Voucher", DBNull.Value);
            else
                AddParameter("Voucher", objEntr.Voucher);
            //AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            if (objEntr.ACCNT_CODE == "")
                AddParameter("ACCNT_CODE", DBNull.Value);
            else
                AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            //AddParameter("InvSN", objEntr.InvSN);
            if (objEntr.InvSN == "")
                AddParameter("InvSN", DBNull.Value);
            else
                AddParameter("InvSN", objEntr.InvSN);
            //AddParameter("Employee", objEntr.Employee);
            if (objEntr.Employee == "")
                AddParameter("Employee", DBNull.Value);
            else
                AddParameter("Employee", objEntr.Employee);

            //AddParameter("ModifiedBy", objEntr.ModifiedBy);
            if (objEntr.ModifiedBy == "")
                AddParameter("ModifiedBy", DBNull.Value);
            else
                AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("ModifiedDate", DateTime.Now);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Update_InvoiceRegister1(InvoiceRegisterInfo objEntr)
        {
            string sql = @"UPDATE InvoiceRegister1 SET 
                    OurRef      = @OurRef,
                    IssueDate   = @IssueDate,
                    ReceiptDate = @ReceiptDate,
                    Amt         = @Amt,
                    OtherAmt    = @OtherAmt,
                    Currency    = @Currency,
                    PymtRef     = @PymtRef,
                    PaidDate    = @PaidDate,
                    ReturnedDate= @ReturnedDate,
                    PaymentTerm = @PaymentTerm,
                    DueDate     = @DueDate,
                    Description = @Description,
                    Contract    = @Contract,
                    DeliveryDate= @DeliveryDate,                 
                    Department  = @Department,
                    Remark1     = @Remark1,
                    Remark2     = @Remark2,
                    Remark3     = @Remark3,
                    Mark        = @Mark,
                    MarkTrm     = @MarkTrm,                            
                    VendorCode  = @VendorCode,
                    CvtRate     = @CvtRate,                  
                    Budget      = @Budget,
                    VAT         = @VAT,
                    VATOther    = @VATOther,
                    VATPercent  = @VATPercent,
                    Originality = @Originality,
                    CIT         = @CIT,
                    PayableAmt  = @PayableAmt,
                    ExpenseAmt  = @ExpenseAmt,
                    aYear       = @aYear,
                    PayableAmtOther = @PayableAmtOther,
                    Business    = @Business,
                    Period      = @Period,
                    Status      = @Status,
                    Voucher     = @Voucher,
                    ACCNT_CODE  = @ACCNT_CODE,
                    InvSN       = @InvSN,
                    Employee    = @Employee,                   
                    ModifiedBy  = @ModifiedBy,                  
                    ModifiedDate= @ModifiedDate,
                    InvNo = @InvNo,
                    Creditor = @Creditor	
                WHERE Reference = @Reference AND [LineNo] = @LineNo";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", objEntr.Reference);
            AddParameter("InvNo", objEntr.InvNo);
            AddParameter("Creditor", objEntr.Creditor);
            //AddParameter("OurRef", objEntr.OurRef);
            if (objEntr.OurRef == "")
                AddParameter("OurRef", DBNull.Value);
            else
                AddParameter("OurRef", objEntr.OurRef);
            //AddParameter("IssueDate", objEntr.IssueDate);
            if (objEntr.IssueDate == DateTime.MinValue)
                AddParameter("IssueDate", DBNull.Value);
            else
                AddParameter("IssueDate", objEntr.IssueDate);
            //AddParameter("ReceiptDate", objEntr.ReceiptDate);
            if (objEntr.ReceiptDate == DateTime.MinValue)
                AddParameter("ReceiptDate", DBNull.Value);
            else
                AddParameter("ReceiptDate", objEntr.ReceiptDate);
            //AddParameter("Amt", objEntr.Amt);
            if (objEntr.Amt == 0)
                AddParameter("Amt", DBNull.Value);
            else
                AddParameter("Amt", objEntr.Amt);
            //AddParameter("OtherAmt", objEntr.OtherAmt);
            if (objEntr.OtherAmt == 0)
                AddParameter("OtherAmt", DBNull.Value);
            else
                AddParameter("OtherAmt", objEntr.OtherAmt);
            //AddParameter("Currency", objEntr.Currency);
            if (objEntr.Currency == "")
                AddParameter("Currency", DBNull.Value);
            else
                AddParameter("Currency", objEntr.Currency);
            //AddParameter("PymtRef", objEntr.PymtRef);
            if (objEntr.PymtRef == "")
                AddParameter("PymtRef", DBNull.Value);
            else
                AddParameter("PymtRef", objEntr.PymtRef);
            //AddParameter("ReturnedDate", objEntr.ReturnedDate);
            if (objEntr.ReturnedDate == DateTime.MinValue)
                AddParameter("ReturnedDate", DBNull.Value);
            else
                AddParameter("ReturnedDate", objEntr.ReturnedDate);
            //AddParameter("PaidDate", objEntr.PaidDate);
            if (objEntr.PaidDate == DateTime.MinValue)
                AddParameter("PaidDate", DBNull.Value);
            else
                AddParameter("PaidDate", objEntr.PaidDate);
            //AddParameter("PaymentTerm", objEntr.PaymentTerm);
            if (objEntr.PaymentTerm == -1)
                AddParameter("PaymentTerm", DBNull.Value);
            else
                AddParameter("PaymentTerm", objEntr.PaymentTerm);
            //AddParameter("DueDate", objEntr.DueDate);
            if (objEntr.DueDate == DateTime.MinValue)
                AddParameter("DueDate", DBNull.Value);
            else
                AddParameter("DueDate", objEntr.DueDate);
            //AddParameter("Description", objEntr.Description);
            if (objEntr.Description == "")
                AddParameter("Description", DBNull.Value);
            else
                AddParameter("Description", objEntr.Description);
            //AddParameter("Contract", objEntr.Contract);
            if (objEntr.Contract == "")
                AddParameter("Contract", DBNull.Value);
            else
                AddParameter("Contract", objEntr.Contract);
            //AddParameter("DeliveryDate", objEntr.DeliveryDate);
            if (objEntr.DeliveryDate == DateTime.MinValue)
                AddParameter("DeliveryDate", DBNull.Value);
            else
                AddParameter("DeliveryDate", objEntr.DeliveryDate);

            //AddParameter("Department", objEntr.Department);
            if (objEntr.Department == "")
                AddParameter("Department", DBNull.Value);
            else
                AddParameter("Department", objEntr.Department);
            //AddParameter("Remark1", objEntr.Remark1);
            if (objEntr.Remark1 == "")
                AddParameter("Remark1", DBNull.Value);
            else
                AddParameter("Remark1", objEntr.Remark1);
            //AddParameter("Remark2", objEntr.Remark2);
            if (objEntr.Remark2 == "")
                AddParameter("Remark2", DBNull.Value);
            else
                AddParameter("Remark2", objEntr.Remark2);
            //AddParameter("Remark3", objEntr.Remark3);
            if (objEntr.Remark3 == "")
                AddParameter("Remark3", DBNull.Value);
            else
                AddParameter("Remark3", objEntr.Remark3);
            //AddParameter("Mark", objEntr.Mark);
            if (objEntr.Mark == -1)
                AddParameter("Mark", DBNull.Value);
            else
                AddParameter("Mark", objEntr.Mark);


            //AddParameter("MarkTrm", objEntr.MarkTrm);
            if (objEntr.MarkTrm == -1)
                AddParameter("MarkTrm", DBNull.Value);
            else
                AddParameter("MarkTrm", objEntr.MarkTrm);


            //AddParameter("VendorCode", objEntr.VendorCode);
            if (objEntr.VendorCode == "")
                AddParameter("VendorCode", DBNull.Value);
            else
                AddParameter("VendorCode", objEntr.VendorCode);
            //AddParameter("LineNo", objEntr.LineNo);
            if (objEntr.LineNo == 0)
                AddParameter("LineNo", 1);
            else
                AddParameter("LineNo", objEntr.LineNo);
            //AddParameter("CvtRate", objEntr.CvtRate);
            if (objEntr.CvtRate == 0)
                AddParameter("CvtRate", DBNull.Value);
            else
                AddParameter("CvtRate", objEntr.CvtRate);

            //AddParameter("Budget", objEntr.Budget);
            if (objEntr.Budget == "")
                AddParameter("Budget", DBNull.Value);
            else
                AddParameter("Budget", objEntr.Budget);
            //AddParameter("VAT", objEntr.VAT);
            if (objEntr.VAT == 0)
                AddParameter("VAT", DBNull.Value);
            else
                AddParameter("VAT", objEntr.VAT);
            //AddParameter("VATOther", objEntr.VATOther);
            if (objEntr.VATOther == 0)
                AddParameter("VATOther", DBNull.Value);
            else
                AddParameter("VATOther", objEntr.VATOther);
            //AddParameter("VATPercent", objEntr.VATPercent);
            if (objEntr.VATPercent == 0)
                AddParameter("VATPercent", DBNull.Value);
            else
                AddParameter("VATPercent", objEntr.VATPercent);
            //AddParameter("Originality", objEntr.Originality);
            if (objEntr.Originality == "")
                AddParameter("Originality", DBNull.Value);
            else
                AddParameter("Originality", objEntr.Originality);
            //AddParameter("CIT", objEntr.CIT);
            if (objEntr.CIT == 0)
                AddParameter("CIT", DBNull.Value);
            else
                AddParameter("CIT", objEntr.CIT);
            //AddParameter("PayableAmt", objEntr.PayableAmt);
            if (objEntr.PayableAmt == 0)
                AddParameter("PayableAmt", DBNull.Value);
            else
                AddParameter("PayableAmt", objEntr.PayableAmt);
            //AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            if (objEntr.ExpenseAmt == 0)
                AddParameter("ExpenseAmt", DBNull.Value);
            else
                AddParameter("ExpenseAmt", objEntr.ExpenseAmt);
            //AddParameter("aYear", objEntr.aYear);
            if (objEntr.aYear == 0)
                AddParameter("aYear", DBNull.Value);
            else
                AddParameter("aYear", objEntr.aYear);
            //AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            if (objEntr.PayableAmtOther == 0)
                AddParameter("PayableAmtOther", DBNull.Value);
            else
                AddParameter("PayableAmtOther", objEntr.PayableAmtOther);
            //AddParameter("Business", objEntr.Business);
            if (objEntr.Business == "")
                AddParameter("Business", DBNull.Value);
            else
                AddParameter("Business", objEntr.Business);
            //AddParameter("Period", objEntr.Period);
            if (objEntr.Period == -1)
                AddParameter("Period", DBNull.Value);
            else
                AddParameter("Period", objEntr.Period);
            //AddParameter("Status", objEntr.Status);
            if (objEntr.Status == "")
                AddParameter("Status", DBNull.Value);
            else
                AddParameter("Status", objEntr.Status);
            //AddParameter("Voucher", objEntr.Voucher);
            if (objEntr.Voucher == "")
                AddParameter("Voucher", DBNull.Value);
            else
                AddParameter("Voucher", objEntr.Voucher);
            //AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            if (objEntr.ACCNT_CODE == "")
                AddParameter("ACCNT_CODE", DBNull.Value);
            else
                AddParameter("ACCNT_CODE", objEntr.ACCNT_CODE);
            //AddParameter("InvSN", objEntr.InvSN);
            if (objEntr.InvSN == "")
                AddParameter("InvSN", DBNull.Value);
            else
                AddParameter("InvSN", objEntr.InvSN);
            //AddParameter("Employee", objEntr.Employee);
            if (objEntr.Employee == "")
                AddParameter("Employee", DBNull.Value);
            else
                AddParameter("Employee", objEntr.Employee);

            //AddParameter("ModifiedBy", objEntr.ModifiedBy);
            if (objEntr.ModifiedBy == "")
                AddParameter("ModifiedBy", DBNull.Value);
            else
                AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("ModifiedDate", DateTime.Now);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }


        public string Delete_InvoiceRegister(
            String Reference,
            Int16 LineNo
        )
        {
            openConnection();
            InitCommand(@"DELETE FROM InvoiceRegister
                WHERE  (Reference = @Reference) AND ([LineNo] = @LineNo)");

            AddParameter("Reference", Reference);
            AddParameter("LineNo", LineNo);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_InvoiceRegister1(
           String Reference,
            Int16 LineNo
        )
        {
            openConnection();
            InitCommand(@"DELETE FROM InvoiceRegister1
                WHERE  (Reference = @Reference) AND ([LineNo] = @LineNo)");

            AddParameter("Reference", Reference);
            AddParameter("LineNo", LineNo);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist_InvoiceRegister(String InvNo, String VendorCode = null, Int16 LineNo = -1)
        {
            InitConnect();
            string sql = @"SELECT TOP 1 Reference
                FROM  InvoiceRegister
                WHERE 	@InvNo = InvNo ";
            if (VendorCode != null) sql += " AND @VendorCode = VendorCode ";
            if (LineNo != -1) sql += " AND @LineNo = [LineNo] ";
            InitCommand(sql, CommandType.Text);

            AddParameter("InvNo", InvNo);
            if (VendorCode != null) AddParameter("VendorCode", VendorCode);
            if (LineNo != -1) AddParameter("LineNo", LineNo);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_InvoiceRegister1(String InvNo, String VendorCode = null, Int16 LineNo = -1)
        {
            InitConnect();
            string sql = @"SELECT TOP 1 Reference
                FROM  InvoiceRegister1
                WHERE 	@InvNo = InvNo ";
            if (VendorCode != null) sql += " AND @VendorCode = VendorCode ";
            if (LineNo != -1) sql += " AND @LineNo = [LineNo] ";
            InitCommand(sql, CommandType.Text);

            AddParameter("InvNo", InvNo);
            if (VendorCode != null) AddParameter("VendorCode", VendorCode);
            if (LineNo != -1) AddParameter("LineNo", LineNo);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        #endregion Method


        public string getNextRefHV(ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand("SELECT MAX(Reference) FROM InvoiceRegister1", CommandType.Text);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp) + 1;
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret.ToString();
        }



        public string getNextRefHL(ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand("SELECT MAX(Reference) FROM InvoiceRegister", CommandType.Text);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp) + 1;
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret.ToString();
        }

        public bool IsExistInSun(string db, string ourRef, string connectString = "")
        {
            string sql = @"SELECT TOP 1 TREFERENCE
                FROM  SALFLDG" + db + @"
                WHERE 	TREFERENCE = @TREFERENCE";
            if (connectString == "") connectString = ConnectionString;
            SqlConnection conn = new SqlConnection(connectString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            var p = adapter.SelectCommand.CreateParameter();
            p.ParameterName = "TREFERENCE";
            p.Value = ourRef;
            adapter.SelectCommand.Parameters.Add(p);
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex) { }
            return dt.Rows.Count > 0;
        }



        public bool IsValidPeriod(int p)
        {
            InitConnect();
            string sql = @"SELECT PeriodFrom, PeriodTo
                FROM  Parameters
                WHERE @Period BETWEEN PeriodFrom AND PeriodTo";
            InitCommand(sql, CommandType.Text);

            AddParameter("Period", p);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1) return true;
            return false;
        }

        public bool IsExistInExpenseClaim(string BusID, string ourRef)
        {
            InitConnect();
            string sql = @"SELECT TOP 1 Reference
                FROM  ExpenseClaim
                WHERE 	Bussiness = @BusID AND OurRef = @OurRef";
            InitCommand(sql, CommandType.Text);

            AddParameter("BusID", BusID);
            AddParameter("OurRef", ourRef);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list.Rows.Count > 0;
        }

        public decimal getConvRateHL(DateTime cur, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT TOP 1 CvtRate
                FROM  InvoiceRegister
                WHERE 	(YEAR(ReceiptDate)*1000 + MONTH(ReceiptDate))= @Period
                ORDER BY ReceiptDate DESC, Reference DESC";
            InitCommand(sql, CommandType.Text);

            AddParameter("Period", cur.Year * 1000 + cur.Month);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        public decimal getConvRateHV(DateTime cur, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT TOP 1 CvtRate
                FROM  InvoiceRegister1
                WHERE 	(YEAR(ReceiptDate)*1000 + MONTH(ReceiptDate))= @Period
                ORDER BY ReceiptDate DESC, Reference DESC";
            InitCommand(sql, CommandType.Text);

            AddParameter("Period", cur.Year * 1000 + cur.Month);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getTotalAmtHL(ref string sErr, int reference = -1, int lineno = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister
                WHERE ((@Reference = -1) OR (Reference <> @Reference AND [LineNo] <> @LineNo))";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", reference);
            AddParameter("LineNo", lineno);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getTotalAmtHV(ref string sErr, int reference = -1, int lineno = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister1
                WHERE ((@Reference = -1) OR (Reference <> @Reference AND [LineNo] <> @LineNo))";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", reference);
            AddParameter("LineNo", lineno);


            try
            {
                ret = Convert.ToDecimal(executeScalar());
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_TransmitHL(InvoiceRegisterInfo obj)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister SET
                    TransMitRef     = @TransMitRef,
                    TransMitDate    = @TransMitDate,
                    TransMitStat    = @TransMitStat,
                    DeliveryDate    = @DeliveryDate,
                    Receiver        = @Receiver
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", obj.Reference);
            AddParameter("LineNo", obj.LineNo);
            AddParameter("TransMitRef", obj.TransMitRef);
            AddParameter("TransMitDate", obj.TransMitDate);
            AddParameter("TransMitStat", obj.TransMitStat);
            AddParameter("DeliveryDate", obj.DeliveryDate);
            AddParameter("Receiver", obj.Receiver);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public string Update_TransmitHV(InvoiceRegisterInfo obj)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister1 SET
                    TransMitRef     = @TransMitRef,
                    TransMitDate    = @TransMitDate,
                    TransMitStat    = @TransMitStat,
                    DeliveryDate    = @DeliveryDate,
                    Receiver        = @Receiver
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", obj.Reference);
            AddParameter("LineNo", obj.LineNo);
            AddParameter("TransMitRef", obj.TransMitRef);
            AddParameter("TransMitDate", obj.TransMitDate);
            AddParameter("TransMitStat", obj.TransMitStat);
            AddParameter("DeliveryDate", obj.DeliveryDate);
            AddParameter("Receiver", obj.Receiver);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public string Update_ReturnHL(InvoiceRegisterInfo obj)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister SET
                    ReturnRef       = @ReturnRef,
                    ReturnedDate    = @ReturnedDate
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", obj.Reference);
            AddParameter("LineNo", obj.LineNo);
            AddParameter("ReturnRef", obj.ReturnRef);
            AddParameter("ReturnedDate", obj.ReturnedDate);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public string Update_ReturnHV(InvoiceRegisterInfo obj)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister1 SET
                    ReturnRef       = @ReturnRef,
                    ReturnedDate    = @ReturnedDate
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", obj.Reference);
            AddParameter("LineNo", obj.LineNo);
            AddParameter("ReturnRef", obj.ReturnRef);
            AddParameter("ReturnedDate", obj.ReturnedDate);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public DataTable getTotalAmtsHV(ref string sErr, int reference)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(PayableAmt),0) as PayableAmt, isnull(SUM(PayableAmtOther),0) as PayableAmtOther, isnull(SUM(PayableAmt*VATPercent/100),0) as VAT
                FROM  InvoiceRegister1
                WHERE Reference = @Reference";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", reference);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public DataTable getTotalAmtsHL(ref string sErr, int reference)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(PayableAmt),0) as PayableAmt, isnull(SUM(PayableAmtOther),0) as PayableAmtOther, isnull(SUM(VAT),0) as VAT
                FROM  InvoiceRegister
                WHERE Reference = @Reference";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", reference);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public DataTable getTransmitRefHL(ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT TransMitRef as Code
                FROM  InvoiceRegister
                GROUP BY TransMitRef
                ORDER BY TransMitRef DESC";

            InitCommand(sql, CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public DataTable getTransmitRefHV(ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT TransMitRef as Code
                FROM  InvoiceRegister1
                GROUP BY TransMitRef
                ORDER BY TransMitRef DESC";

            InitCommand(sql, CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public DataTable getReturnRefHL(ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT ReturnRef as Code
                FROM  InvoiceRegister
                GROUP BY ReturnRef
                ORDER BY ReturnRef DESC";

            InitCommand(sql, CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public DataTable getReturnRefHV(ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT ReturnRef as Code
                FROM  InvoiceRegister1
                GROUP BY ReturnRef
                ORDER BY ReturnRef DESC";

            InitCommand(sql, CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            return list;
        }

        public string updateFieldHL(string name, object value, InvoiceRegisterInfo inf)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister SET
                    " + name + @" = @value
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", inf.Reference);
            AddParameter("LineNo", inf.LineNo);
            AddParameter("value", value);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public string UpdateFieldHV(string name, object value, InvoiceRegisterInfo inf)
        {
            string sErr = "";
            openConnection();
            string sql = @"UPDATE InvoiceRegister1 SET
                    " + name + @" = @value
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", inf.Reference);
            AddParameter("LineNo", inf.LineNo);
            AddParameter("value", value);

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = ex.Message; }

            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return sErr;
        }

        public int getMaxLineNoHL(int refer, ref string sErr)
        {
            int ret = 0;
            openConnection();
            string sql = @"SELECT MIN([LineNo]) as [LineNo]
                FROM  InvoiceRegister
                WHERE Reference = @Reference AND [LineNo] > 5000";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToInt32(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public int getMaxLineNoHV(int refer, ref string sErr)
        {
            int ret = 0;
            openConnection();
            string sql = @"SELECT MIN([LineNo]) as [LineNo]
                FROM  InvoiceRegister1
                WHERE Reference = @Reference AND [LineNo] > 5000";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToInt32(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getContractTotalAmtHL(string code, ref string sErr, int refer = -1, int line = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister
                WHERE (Reference <> @Reference OR [LineNo] <> @LineNo) AND Contract = @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("Contract", code);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getContractTotalAmtHV(string code, ref string sErr, int refer = -1, int line = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister1
                WHERE (Reference <> @Reference OR [LineNo] <> @LineNo) AND Contract = @Contract";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("Contract", code);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }


        public decimal getBudgetTotalAmtHL(string code, int year, ref string sErr, int refer = -1, int line = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister
                WHERE (Reference <> @Reference OR [LineNo] <> @LineNo) AND Budget = @Budget AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("Budget", code);
            AddParameter("Year", year);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getBudgetTotalAmtHV(string code, int year, ref string sErr, int refer = -1, int line = -1)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(Amt) as Amount
                FROM  InvoiceRegister1
                WHERE (Reference <> @Reference OR [LineNo] <> @LineNo) AND Budget = @Budget AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("Budget", code);
            AddParameter("Year", year);

            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string DeleteHL(int refer, short lineNo)
        {
            string sql = @"DELETE FROM InvoiceRegister 	
                WHERE 	@Reference = Reference AND @LineNo = [LineNo] ";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", lineNo);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string DeleteHV(int refer, short lineNo)
        {
            string sql = @"DELETE FROM InvoiceRegister1	
                WHERE 	@Reference = Reference AND @LineNo = [LineNo] ";
            openConnection();
            InitCommand(sql, CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("LineNo", lineNo);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public int getMaxTransmitRefHL(int year, int month, ref string sErr)
        {
            int ret = 0;
            openConnection();
            InitCommand(@"SELECT MAX(TransMitRef) 
                FROM InvoiceRegister
                WHERE YEAR(TransMitDate) = @Year AND MONTH(TransMitDate) = @Month", CommandType.Text);

            AddParameter("Year", year);
            AddParameter("Month", month);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public int getMaxTransmitRefHV(int year, int month, ref string sErr)
        {
            int ret = 0;
            openConnection();
            InitCommand(@"SELECT MAX(TransMitRef) 
                FROM InvoiceRegister1
                WHERE YEAR(TransMitDate) = @Year AND MONTH(TransMitDate) = @Month", CommandType.Text);

            AddParameter("Year", year);
            AddParameter("Month", month);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public int getMaxReturnRefHL(int year, int month, ref string sErr)
        {
            int ret = 0;
            openConnection();
            InitCommand(@"SELECT MAX(ReturnRef) 
                FROM InvoiceRegister
                WHERE YEAR(ReturnedDate) = @Year AND MONTH(ReturnedDate) = @Month", CommandType.Text);

            AddParameter("Year", year);
            AddParameter("Month", month);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public int getMaxReturnRefHV(int year, int month, ref string sErr)
        {
            int ret = 0;
            openConnection();
            InitCommand(@"SELECT MAX(ReturnRef) 
                FROM InvoiceRegister1
                WHERE YEAR(ReturnedDate) = @Year AND MONTH(ReturnedDate) = @Month", CommandType.Text);

            AddParameter("Year", year);
            AddParameter("Month", month);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public bool IsOurRefModified(int refer, int line, string ourRef)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) OurRef 
                FROM InvoiceRegister 
                WHERE Reference = @Reference AND [LineNo] = @LineNo AND (OurRef is null OR OurRef <> @OurRef) ";
            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("OurRef", ourRef);
            DataTable dt = new DataTable();
            try
            {

                dt = executeSelect();

            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return dt.Rows.Count > 0;
        }

        public bool IsOurRefModified1(int refer, int line, string ourRef)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) OurRef 
                FROM InvoiceRegister1
                WHERE Reference = @Reference AND [LineNo] = @LineNo AND (OurRef is null OR OurRef <> @OurRef) ";
            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("LineNo", line);
            AddParameter("OurRef", ourRef);
            DataTable dt = new DataTable();
            try
            {

                dt = executeSelect();

            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return dt.Rows.Count > 0;
        }

        public bool IsExist(int refer, short line)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) OurRef 
                FROM InvoiceRegister
                WHERE Reference = @Reference AND [LineNo] = @LineNo";

            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("LineNo", line);

            DataTable dt = new DataTable();

            try { dt = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }

            return dt.Rows.Count > 0;
        }

        public bool IsExist1(int refer, short line)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) OurRef 
                FROM InvoiceRegister1
                WHERE Reference = @Reference AND [LineNo] = @LineNo";
            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("LineNo", line);

            DataTable dt = new DataTable();
            try { dt = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }

            return dt.Rows.Count > 0;
        }

        public string GetCreatorHL(string value)
        {
            string sErr = "";
            string res = "";
            InitConnect();
            string sql = @"SELECT MAX(Creditor)
                FROM InvoiceRegister
                WHERE VendorCode = @VendorCode ";
            InitCommand(sql, CommandType.Text);
            AddParameter("VendorCode", value);

            try
            {
                object o = executeScalar();
                if (o != null) res = o.ToString();
            }
            catch (Exception ex) { sErr = ex.Message; }

            return res;
        }

        public string GetCreatorHV(string value)
        {
            string sErr = "";
            string res = "";
            InitConnect();
            string sql = @"SELECT MAX(Creditor)
                FROM InvoiceRegister1
                WHERE VendorCode = @VendorCode ";
            InitCommand(sql, CommandType.Text);
            AddParameter("VendorCode", value);

            try
            {
                object o = executeScalar();
                if (o != null) res = o.ToString();
            }
            catch (Exception ex) { sErr = ex.Message; }

            return res;
        }

        public bool IsExistInvoiceNoHL(string invNo, int refer, string creditor)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) InvNo 
                FROM InvoiceRegister
                WHERE InvNo = @InvNo AND Creditor = @Creditor AND (Reference <> @Reference)";
            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("Creditor", creditor);
            AddParameter("InvNo", invNo);

            DataTable dt = new DataTable();
            try { dt = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }

            return dt.Rows.Count > 0;
        }

        public bool IsExistInvoiceNoHV(string invNo, int refer, string creditor)
        {
            string sErr = "";
            InitConnect();
            string sql = @"SELECT TOP(1) InvNo 
                FROM InvoiceRegister1
                WHERE InvNo = @InvNo AND Creditor = @Creditor AND (Reference <> @Reference)";
            InitCommand(sql, CommandType.Text);
            AddParameter("Reference", refer);
            AddParameter("Creditor", creditor);
            AddParameter("InvNo", invNo);

            DataTable dt = new DataTable();
            try { dt = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }

            return dt.Rows.Count > 0;
        }
    }
}
